package com.deepinjava.dbbmlite;

import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.nio.ByteBuffer;
import java.sql.*;
import java.util.ArrayList;


import static com.deepinjava.dbbmlite.Application.*;

public class BenchService {

    protected Connection[] conn;
    protected BaseSqlProvider sqlPrd;
    private ArrayList<long[]> itemList;
    private ArrayList<RowSet> rowList;
    private ArrayList<ArrayList<String>> tempList;


    public BenchService() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException {
        itemList = loadtoMemByData(THREAD_COUNT, ITEM_COUNT);
        tempList = new ArrayList<>(THREAD_COUNT);
        rowList = new ArrayList<>(THREAD_COUNT);
        conn = new Connection[THREAD_COUNT];

        sqlPrd = (BaseSqlProvider) Class.forName(
                "com.deepinjava.dbbmlite." + TASK_PROP.getProperty("dialect") + "Provider").getDeclaredConstructor().newInstance();

        for (int i = 0; i < conn.length; i++) {
            conn[i] = DriverManager.getConnection(
                    TASK_PROP.getProperty("url"), TASK_PROP.getProperty("username"), TASK_PROP.getProperty("password"));
            conn[i].setAutoCommit(false);
        }
    }

    public void reConnect() throws SQLException {
        for (int i = 0; i < conn.length; i++) {

            conn[i].close();
            conn[i] = DriverManager.getConnection(
                    TASK_PROP.getProperty("url"), TASK_PROP.getProperty("username"), TASK_PROP.getProperty("password"));;
            conn[i].setAutoCommit(false);
        }
    }


    public ArrayList<long[]> loadtoMemByData(int tnum, int count) {
        System.out.println("==============加载数据到内存====================");
        ArrayList<long[]> items = new ArrayList<>(tnum);
        byte[] ba = new byte[8];
        try {
            System.out.println(System.getProperty("user.dir"));
            String path = Application.class.getClassLoader().getResource("").getPath();
            System.out.println(path);
            DataInputStream is = new DataInputStream(
                    new BufferedInputStream(new FileInputStream(
                            "E:\\\\Downloads\\\\SuperPI190\\\\pi_data_full.dat")));
            for (int i = 0; i < tnum; i++) {
                long[] sa = new long[count];
                for (int j = 0; j < count; j++) {
                    is.read(ba, 3, 5);
                    sa[j] = bytesToLong(ba);
//                    sa[j]= is.readLong();
                }
                items.add(sa);
            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return items;
    }

    public ArrayList<long[]> loadtoMem(int tnum, int count) {
        System.out.println("==============加载数据到内存====================");
        ArrayList<long[]> items = new ArrayList<>(tnum);
        String path = Application.class.getClassLoader().getResource("").getPath();


        try {
            System.out.println(path);
            BufferedReader in = new BufferedReader(new FileReader(new File(path + "pi_data.txt")));
            String line;
            for (int i = 0; i < tnum; i++) {
                long[] sa = new long[count];

                for (int j = 0; j < count; j++) {
                    line = in.readLine();
                    sa[j] = Long.parseLong(line.trim());
                }
                items.add(sa);
            }


        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return items;

    }

    public long bytesToLong(byte[] bytes) {
        ByteBuffer buffer = ByteBuffer.allocate(8);
        buffer.put(bytes, 0, bytes.length);
        buffer.flip();//need flip
        return buffer.getLong();
    }

    public void wirteBinary(ArrayList<long[]> items) {
        ByteBuffer buffer = ByteBuffer.allocate(8);
        try {
            DataOutputStream os = new DataOutputStream(
                    new BufferedOutputStream(new FileOutputStream(
                            Application.PATH + "pi_data_normal_gen.dat")));

            for (long[] item : items) {
                for (int j = 0; j < item.length; j++) {
                    buffer.putLong(0, item[j]);
                    os.write(buffer.array(), 3, 5);
                }
            }
            os.flush();
            os.close();
            System.out.println("文件生成结束");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void initDb() throws SQLException {
        Statement stmt = conn[0].createStatement();

        String[] intDbSql = sqlPrd.getIniDbSql();

        for (int i = 0; i < intDbSql.length; i++) {
            try {
                System.out.println("执行DDL：\n" + intDbSql[i]);
                stmt.executeUpdate(intDbSql[i]);
                conn[0].commit();
            } catch (SQLException e) {
                conn[0].rollback();
                e.printStackTrace();
            }
        }
        stmt.close();
    }

    public long addData(int tid) throws SQLException {
        String execSql = sqlPrd.getInsertSql();

        System.out.println("线程T" + tid + "执行SQL：" + execSql);

        long[] item = itemList.get(tid);

        PreparedStatement ps = conn[tid].prepareStatement(execSql);
        int pagesize = PAGE_SIZE;

        long rowcount = 0;

        String s = (tid == 0) ? "3." :
                String.format("%10s", Long.toString
                        (itemList.get(tid - 1)[ITEM_COUNT - 1]))
                        .replace(' ', '0')
                        .substring(7).trim();

        for (int i = 0; i < item.length; i++) {
            String scol = String.format("%10s", Long.toString(item[i])).replace(' ', '0');
            ps.setInt(1, tid * ITEM_COUNT + i);
            ps.setString(2, scol);
            ps.setLong(3, item[i]);
            ps.setDouble(4, (double) item[i]);
            ps.setInt(5, tid);
            ps.setString(6, s + scol);
            s = scol.substring(7);

            ps.addBatch();

            if ((i % pagesize) == pagesize - 1) {
                System.out.println("线程T" + tid + "Page：" +i);
                int[] c = ps.executeBatch();
                System.out.println("线程T" + tid + ps);
                conn[tid].commit();
                rowcount += c.length;
            }
        }

        conn[tid].commit();
//      itemList.set(tid,null);
        ps.close();

        return rowcount;

    }

    public long updateData(int tid) throws SQLException {
        String execSql = sqlPrd.getUpdateSql();
        long rowcount = 0;

        System.out.println("线程T" + tid + "执行SQL:" + execSql);

        for (int i = 0; i < ITEM_COUNT / PAGE_SIZE; i++) {

            PreparedStatement ps = conn[tid].prepareStatement(execSql);

            System.out.println("线程T" + tid + "Page:" + i + "执行SQL:" + execSql);
            ps = conn[tid].prepareStatement(execSql);
            ps.setInt(1, tid * ITEM_COUNT + i * PAGE_SIZE); //起始位置
            ps.setInt(2, tid * ITEM_COUNT + i * PAGE_SIZE + PAGE_SIZE - 1); //结束位置

            rowcount += ps.executeUpdate();
            conn[tid].commit();
            ps.close();
        }
        return rowcount;
    }

    public long readData(int tid) throws SQLException {
        String execSql = sqlPrd.getReadSql();
        System.out.println("线程T" + tid + "执行SQL：" + execSql);
        PreparedStatement ps = null;
        ResultSet rs = null;
        int rowcount = 0;

        ps = conn[tid].prepareStatement(execSql);
        ps.setLong(1, tid * ITEM_COUNT);
        ps.setLong(2, tid * ITEM_COUNT + ITEM_COUNT - 1);

        rs = ps.executeQuery();

        int cc = rs.getMetaData().getColumnCount();
        while (rs.next()) {
            for (int j = 1; j <= cc ; j++) {Object o = rs.getObject(j);}
            rowcount++;
        }

        ps.close();

        return rowcount;
    }

    public long pageData(int tid) throws SQLException {
        String execSql = sqlPrd.getReadTopSql();

        System.out.println("线程T" + tid + "执行SQL：" + execSql);

        PreparedStatement ps = conn[tid].prepareStatement(execSql);
        ResultSet rs = null;
        int rowcount = 0;

        long lasticol = 0;
        int pagesize = 1000;

        ArrayList<String> tempIdList = new ArrayList<>(ITEM_COUNT / pagesize);

        for (int i = 0; i < ITEM_COUNT / pagesize; i++) {

            ps.setLong(1, tid * ITEM_COUNT);
            ps.setLong(2, tid * ITEM_COUNT + ITEM_COUNT - 1);
            ps.setLong(3, lasticol);
            ps.setLong(4, pagesize);

            rs = ps.executeQuery();

            StringBuffer idsb = new StringBuffer();
            int cc = rs.getMetaData().getColumnCount();
            while (rs.next()) {
//              for (int j = 1; j <= cc ; j++) {Object o = rs.getObject(j);}
                idsb = idsb.append(rs.getLong("ID")).append(",");
                lasticol = rs.getLong("ICOL");
                rowcount++;
                // System.out.println("rowcount,lasticol:" + rowcount + "," + lasticol);
            }
            tempIdList.add(idsb.substring(0, idsb.length() - 1));
        }
        tempList.add(tempIdList);
        return rowcount;
    }

    public long writeData(int tid) throws SQLException, InterruptedException {

        String execSqlTemplate = sqlPrd.getWriteSql();
        System.out.println("线程T" + tid + "执行SQL:" + execSqlTemplate);

        PreparedStatement ps = null;
        int rowcount = 0;

        for (String s : tempList.get(tid)) {
            String execSql = execSqlTemplate.replaceAll("#",s);
            ps = conn[tid].prepareStatement(execSql);
            //System.out.println("线程T" + tid + "Write:" + execSql);
            rowcount += ps.executeUpdate();
            conn[tid].commit();
            ps.close();
        }
        return rowcount;
    }

    public long copyData(int tid) throws SQLException {
        String execSql = sqlPrd.getCopySql();
        System.out.println("线程T" + tid + "执行SQL:" + execSql);
        PreparedStatement ps = conn[tid].prepareStatement(execSql);

        int rowcount = 0;
        int spiltAmount = ITEM_COUNT / PAGE_SIZE;
        long spiltCount = 10_000_000_000L / spiltAmount;

        for (int i = 0; i < spiltAmount; i++) {

            ps.setInt(1, tid * ITEM_COUNT); //起始位置
            ps.setInt(2, (tid + 1) * ITEM_COUNT - 1); //结束位置
            ps.setLong(3, i * spiltCount);
            ps.setLong(4, (i + 1) * spiltCount - 1);

            rowcount += ps.executeUpdate();
            conn[tid].commit();
        }

        ps.close();
        return rowcount;
    }

    public long sumData(int tid) throws SQLException {
        String execSql;
        System.out.println("线程T" + tid + "执行SQL:");

        Statement stmt = conn[tid].createStatement();
        int rowcount = 0;
        int maxLeftIndex = 5;

        for (int i = 1; i <= maxLeftIndex; i++) {
            if ((i - 1) % THREAD_COUNT == tid) {
                execSql = sqlPrd.getSumSql(i);
                int c = 0;
                System.out.println("线程T" + tid + "执行数据统计语句" + execSql);
                c = stmt.executeUpdate(execSql);
                rowcount += c;
                System.out.println("T" + tid + "-左" + i + "位分组：" + c);
            }
        }
        conn[tid].commit();
        stmt.close();
        return rowcount;
    }

    public long findData(int tid) throws SQLException {
        String execSql;
        System.out.println("线程T" + tid + "执行SQL：");

        Statement stmt = conn[tid].createStatement();
        int rowCount = 0;
        int maxFindNumber = 9;


        for (int i = 0; i <= maxFindNumber; i++) {
            if (i % THREAD_COUNT == tid) {
                execSql = sqlPrd.getFindSql(i);
                int c = 0;
                System.out.println(execSql);
                c = stmt.executeUpdate(execSql);
                rowCount += c;
                System.out.println("T" + tid + "---find" + i + i + i + i + ":" + c);
            }
        }

        conn[tid].commit();
        stmt.close();

        return rowCount;
    }

    public long matchData(int tid) throws SQLException {
        String execSql;
        System.out.println("线程T" + tid + "执行SQL：");

        Statement stmt = conn[tid].createStatement();
        int rowCount = 0;
        int maxFindNumber = 9;


        for (int i = 0; i <= maxFindNumber; i++) {
            if (i % THREAD_COUNT == tid) {
                execSql = sqlPrd.getMatchSql(i);
                int c = 0;
                System.out.println(execSql);
                c = stmt.executeUpdate(execSql);
                rowCount += c;
                System.out.println("T" + tid + "---match" + i + i + i + i + ":" + c);
            }
        }

        conn[tid].commit();
        stmt.close();

        return rowCount;
    }

    public long delData(int tid) throws SQLException {
        //首先删除bak表数据并提交
        String execSql = "";

        int rowcount = 0;

        for (int i = 0; i < ITEM_COUNT / PAGE_SIZE; i++) {

            PreparedStatement ps;

            execSql = sqlPrd.getDelbakSql();
            System.out.println("线程T" + tid + "Page:"+i+"执行SQL:" + execSql);
            ps = conn[tid].prepareStatement(execSql);
            ps.setInt(1, tid * ITEM_COUNT + i*PAGE_SIZE); //起始位置
            ps.setInt(2, tid * ITEM_COUNT + i*PAGE_SIZE + PAGE_SIZE - 1); //结束位置

            rowcount += ps.executeUpdate();
            conn[tid].commit();
            ps.close();

            //然后删除元表数据并回滚
            execSql = sqlPrd.getDelSql();
            System.out.println("线程T" + tid + "Page:"+i+"执行SQL:" + execSql);
            ps = conn[tid].prepareStatement(execSql);
            ps.setInt(1, tid * ITEM_COUNT + i*PAGE_SIZE); //起始位置
            ps.setInt(2, tid * ITEM_COUNT + i*PAGE_SIZE + PAGE_SIZE - 1); //结束位置

            rowcount += ps.executeUpdate();
            conn[tid].rollback();
            ps.close();
        }
        return rowcount;
    }

    public long editData(int tid) throws SQLException {
        long readcost = 0, writecost = 0;

        String execSql = sqlPrd.getReadTopSql();
        System.out.println("线程T" + tid + "执行SQL：" + execSql);
        String execSqlTemplate = sqlPrd.getWriteSql();
        System.out.println("线程T" + tid + "执行SQL:" + execSqlTemplate);

        PreparedStatement ps = conn[tid].prepareStatement(execSql);
        ResultSet rs = null;
        int rowcount = 0;
        long lasticol = 0;
        int pagesize = PAGE_SIZE;

        try {

            if (tid == 3) {
                System.out.println();
            }


        for (int i = 0; i < ITEM_COUNT / pagesize; i++) {

            ps.setLong(1, tid * ITEM_COUNT);
            ps.setLong(2, tid * ITEM_COUNT + ITEM_COUNT - 1);
            ps.setLong(3, lasticol);
            ps.setLong(4, pagesize);

            rs = ps.executeQuery();

            StringBuffer idsb = new StringBuffer();
            while (rs.next()) {
                idsb = idsb.append(rs.getLong("ID")).append(",");
                lasticol = rs.getLong("ICOL");
                rowcount++;
                // System.out.println("rowcount,lasticol:" + rowcount + "," + lasticol);
            }

            String execSqlwrite = execSqlTemplate.replaceAll("#",idsb.substring(0, idsb.length() - 1));
            System.out.println("线程T" + tid + "Write:" + i); // execSqlwrite);
            Statement st = conn[tid].createStatement();
            rowcount += st.executeUpdate(execSqlwrite);
            conn[tid].commit();
            st.close();
        }
        }catch (Exception ex)
        {
            System.err.println();
            ex.printStackTrace();
        }
        return rowcount;



        //old
//        String execSql = sqlPrd.getReadTopSql();
//
//        System.out.println("线程T" + tid + "执行read top：" + execSql);
//
//        PreparedStatement ps = null;
//        Statement st = conn[tid].createStatement();
//
//        ResultSet rs = null;
//        int rowcount = 0;
//
//        long readcost = 0, writecost = 0;
//        long lasticol = 0;
//        int pagesize = PAGE_SIZE;
//
//        for (int i = 0; i < ITEM_COUNT / pagesize; i++) {
//
//            ps = conn[tid].prepareStatement(execSql);
//
//            long check = System.currentTimeMillis();
//
//            ps.setLong(1, tid * ITEM_COUNT);
//            ps.setLong(2, tid * ITEM_COUNT + ITEM_COUNT - 1);
//            ps.setLong(3, lasticol);
//            ps.setLong(4, pagesize);
//
//            rs = ps.executeQuery();
//
//            StringBuffer idsb = new StringBuffer();
//
//            while (rs.next()) {
//                idsb = idsb.append(rs.getLong(1)).append(",");
//                lasticol = rs.getLong(3);
////                System.out.println(ids);
//            }
////            System.out.println("线程T" + tid + "Page read "+i+" cost:"+ (System.currentTimeMillis()-check));
//
//            readcost += System.currentTimeMillis() - check;
//            check = System.currentTimeMillis();
//
//            String ids = idsb.substring(0, idsb.length() - 1);
//
//            String writeSql = sqlPrd.getWriteSql().replaceAll("#", ids);
////            System.out.println("线程T" + tid + "执行write in：" + writeSql);
//            rowcount += st.executeUpdate(writeSql);
////            System.out.println("Page write "+i+" cost:"+ (System.currentTimeMillis()-check));
//            writecost += System.currentTimeMillis() - check;
//        }
//
//        System.out.println("线程T" + tid + ":Read cost:" + readcost + ",Write cost:" + writecost);
//
//        conn[tid].commit();
//        rs.close();
//        ps.close();
//        st.close();
//
//        return rowcount;
    }
}